package com.jsp.study.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class BookWithPageDao {

	public Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://118.89.160.58:3306/xbin_db?relaxAutoCommit=true&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&failOverReadOnly=false";
			conn = DriverManager.getConnection(url, "xbin", "xbin0629");
		} catch (Exception e) {
			System.out.println("数据库连接失败");
		}
		return conn;
	}
	
	/**
	 * 分页查询所有信息 
	 * @param page 当前页
	 * @return
	 */
	public List<BookWithPage> find(int page) {
		List<BookWithPage> list = new ArrayList<>();
		Connection conn = getConnection();
		if (conn != null) {
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				String sql = "select * from tb_books order by id desc limit ?,?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, (page - 1) * BookWithPage.PAGE_SIZE);
				ps.setInt(2, BookWithPage.PAGE_SIZE);
				rs = ps.executeQuery();
				while (rs.next()) {
					BookWithPage bookWithPage = new BookWithPage();
					bookWithPage.setId(rs.getInt("id"));
					bookWithPage.setName(rs.getString("name"));
					bookWithPage.setPrice(rs.getDouble("price"));
					bookWithPage.setBookCount(rs.getInt("bookcount"));
					bookWithPage.setAuthor(rs.getString("author"));
					list.add(bookWithPage);
				}
				rs.close();
				ps.close();
				conn.close();
			} catch (Exception e) {
				System.out.println("在操作数据库过程中出现异常");
			}
		}
		return list;
	}
	
	/**
	 * 查询商品总记录数
	 * @return
	 */
	public int findCount() {
		int count = 0;
		Connection conn = getConnection();
		if (conn != null) {
			Statement stmt = null;
			ResultSet rs = null;
			try {
				String sql = "select count(*) from tb_books";
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				while (rs.next()) {
					count = rs.getInt(1);
				}
				rs.close();
				stmt.close();
				conn.close();
			} catch (Exception e) {
				System.out.println("在操作数据库过程中出现异常");
			}
		}
		return count;
	}
	
}
